package com.kandinfo.isbnmanage.repository.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.kandinfo.base.common.repository.AliasToBeanResultTransformerEx;
import com.kandinfo.isbnmanage.dto.IsbnDigDto;
import com.kandinfo.isbnmanage.repository.IsbnDigRepository;

@Repository
public class IsbnDigRepositoryImpl implements IsbnDigRepository{
	
	 @Autowired
	 private EntityManager entityManager;

	@SuppressWarnings("unchecked")
	@Override
	public List<IsbnDigDto> getIsbnDigDtoList() {
		StringBuffer nativeSql = new StringBuffer();
        nativeSql.append(" SELECT ");
        nativeSql.append("     uur.barcode AS barcode ");
        nativeSql.append(" 	   ,COUNT(DISTINCT uur.user_id) AS userCount ");
        nativeSql.append("     ,GROUP_CONCAT(DISTINCT uur.province) AS provinces ");
        nativeSql.append("     ,min(uur.create_ts) AS firstUploadTime ");
        nativeSql.append(" FROM ");
        nativeSql.append("     user_upload_record uur ");
        nativeSql.append(" WHERE ");
        nativeSql.append("     uur.type = 2 ");
        nativeSql.append("     and not exists( ");
        nativeSql.append("     select 1 from homework h where h.barcode = uur.barcode) ");
        nativeSql.append("     and not exists( ");
        nativeSql.append("     select 1 from isbn_manage im where im.barcode = uur.barcode) ");
        nativeSql.append(" GROUP BY uur.barcode ");
        nativeSql.append(" HAVING  ");
        nativeSql.append(" userCount > 1 ");
        
        Query nativeQuery = entityManager.createNativeQuery(nativeSql.toString());
        org.hibernate.Query query = nativeQuery.unwrap(org.hibernate.Query.class);
        query.setResultTransformer(new AliasToBeanResultTransformerEx(IsbnDigDto.class));
        return query.list();
	}
	 
	 
}
